﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OgilvyOne.UKNOW.Entity;
using System.Web;
using OgilvyOne.CommonToolLibrary;

using System.Data;
using OgilvyOne.CommonToolLibrary.MD5;
using OgilvyOne.SqlServerDAL;
using OgilvyOne.BLL.BLLEngine;

namespace OgilvyOne.UKNOW.EntityDB
{
    
    public  class ContactInfoDB
    {
        private const string PageConfigXML = @"XML\PageConfig.xml";
        /// <summary>
        /// 插入联系信息
        /// </summary>
        /// <param name="contactInfo"></param>
        /// <param name="strKey">xml中KEY</param>
        /// <param name="userType">1：中文注册 0：英文注册</param>
        /// <returns></returns>
        public static int Insert(ContactInfo contactInfo,string strKey,string userType)
        {
            int i = -1;
            try
            {
                string sql = string.Empty;
                sql = ConfigListInfoDB.GetItemValue(HttpContext.Current.Server.MapPath(PageConfigXML), "SqlConfig", strKey);
                //sql = sql.Replace("", memberInfo.UserName);
                sql = sql.Replace("[$USERNAME]", contactInfo.UserName);
                sql = sql.Replace("[$USER_TYPE]", userType);
                sql = sql.Replace("[$MOBLIE]", contactInfo.Mobile);
                sql = sql.Replace("[$Register_TIME]", DateTime.Now.ToString());
                sql = sql.Replace("[$STATE]", "0");
                sql = sql.Replace("[$EMAIL]", contactInfo.Email);
                sql = sql.Replace("[$BIRTHDAY]", "''");
                sql = sql.Replace("[$NATURE]", contactInfo.Nature);
               
                i = SQLHelper.ExecuteSql(sql);
            }
            catch(Exception e)
            {
                
            }
            return i;
        }

        public static DataTable SelectByUserName(ContactInfo contactInfo, string strKey)
        {
            string sql = string.Empty;
            sql = ConfigListInfoDB.GetItemValue(HttpContext.Current.Server.MapPath(PageConfigXML), "SqlConfig", strKey);
            sql = sql.Replace("[$USERNAME]", contactInfo.UserName);
            DataTable dt = CommonQuery.GetDataBySql(Env.DefaultConnectionString, sql);
            return dt;
        }
        public static DataTable SelectByUserName(string userName, string strKey)
        {
            string sql = string.Empty;
            sql = ConfigListInfoDB.GetItemValue(HttpContext.Current.Server.MapPath(PageConfigXML), "SqlConfig", strKey);
            sql = sql.Replace("[$USERNAME]", userName);
            DataTable dt =  SQLHelper.Query(sql).Tables[0];
            return dt;
        }

        public static DataTable SelectByMail(string mail, string strKey)
        {
            string sql = string.Empty;
            sql = ConfigListInfoDB.GetItemValue(HttpContext.Current.Server.MapPath(PageConfigXML), "SqlConfig", strKey);
            sql = sql.Replace("[$EMAIL]", mail);
            DataTable dt = SQLHelper.Query(sql).Tables[0];
            return dt;
        }

        /// <summary>
        /// 按时间段进行查询
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="strKey"></param>
        /// <returns></returns>
        public static DataTable Query(string startTime, string endTime, string userType, string strKey)
        {
            string sql = string.Empty;
            sql = ConfigListInfoDB.GetItemValue(HttpContext.Current.Server.MapPath(PageConfigXML), "SqlConfig", strKey);
            sql = sql.Replace("[$StartTime]", startTime);
            sql = sql.Replace("[$EndTime]", endTime);
            sql = sql.Replace("[$USER_TYPE]", userType);
            DataTable dt = SQLHelper.Query(sql).Tables[0];
            return dt;
        }

        public static DataTable QueryByTime(string startTime, string endTime)
        {
            string sql = string.Empty;
            sql = string.Format("select * from UKNOW_Contact_INFO where datediff(dd, Register_TIME, '{0}')<=0 and datediff(dd, Register_TIME, '{1}')>=0", startTime, endTime);
            
            DataTable dt = SQLHelper.Query(sql).Tables[0];
            return dt;
        }
        public static DataTable QueryByAll(string startTime, string endTime,string name,string email)
        {
            string sql = string.Empty;
            sql = string.Format("select * from UKNOW_Contact_INFO where datediff(dd, Register_TIME, '{0}')<=0 and datediff(dd, Register_TIME, '{1}')>=0 and (USERNAME like '%{2}%' or  EMAIL like '%{3}%')", startTime, endTime, name, email);

            DataTable dt = SQLHelper.Query(sql).Tables[0];
            return dt;
        }

        public static DataTable QueryByAll(string startTime, string endTime, string email)
        {
            string sql = string.Empty;
            sql = string.Format("select * from UKNOW_Contact_INFO where datediff(dd, Register_TIME, '{0}')<=0 and datediff(dd, Register_TIME, '{1}')>=0 and  EMAIL like '%{2}%'", startTime, endTime,  email);

            DataTable dt = SQLHelper.Query(sql).Tables[0];
            return dt;
        }
        public static DataTable QueryByAllName(string startTime, string endTime, string name)
        {
            string sql = string.Empty;
            sql = string.Format("select * from UKNOW_Contact_INFO where datediff(dd, Register_TIME, '{0}')<=0 and datediff(dd, Register_TIME, '{1}')>=0 and  USERNAME like '%{2}%'", startTime, endTime, name);

            DataTable dt = SQLHelper.Query(sql).Tables[0];
            return dt;
        }
        public static DataTable SelectById(string Mid)
        {
            string sql = string.Empty;
            sql = string.Format("select * from UKNOW_Contact_INFO where Member_ID={0}", Mid);
            DataTable dt = SQLHelper.Query(sql).Tables[0];
            return dt;
        }
        public static int Update(string idLst, string strKey)
        {
            int i = -1;

            try
            {
                string sql = string.Empty;
                sql = ConfigListInfoDB.GetItemValue(HttpContext.Current.Server.MapPath(PageConfigXML), "SqlConfig", strKey);
                sql = sql.Replace("[$Member_ID]", idLst);
                i = SQLHelper.ExecuteSql(sql);
            }
            catch (Exception e)
            {

            }
            return i;
        }

        public static int Delect(string idLst)
        {
            int i = -1;

            try
            {
                string sql = string.Empty;
                sql = string.Format("delete from UKNOW_Contact_INFO where MEMBER_ID={0}", idLst);
                i = SQLHelper.ExecuteSql(sql);
            }
            catch (Exception e)
            {

            }
            return i;
        }
    }
}
